interface attr {
    tbName?: string
    tableColumns?: any
    tableIndex?: any
    columnAdd?: any
    data?: any
    whereObj?: any
    where?: any
    selects?: any
    orderBy?: any
    limit?: any
    model?: any
    sql?: string
}
/**
 * sqlite 操作基础类
 */
export default class Action {
    dbName: string
    INSERT: number
    UPDATE: number
    QUERY: number
    DELETE: number
    CREATE: number
    UPDATE_BY_ID: number
    SAVE_OR_UPDATE: number
    QUERY_CUSTOM: number
    tableName: string
    columns: string
    constructor() {
        this.dbName = 'COMICFVIPMERCHANTIMDB'

        this.INSERT = 1
        this.UPDATE = 2
        this.QUERY = 3
        this.DELETE = 4
        this.CREATE = 5
        this.UPDATE_BY_ID = 6
        this.SAVE_OR_UPDATE = 7
        this.QUERY_CUSTOM = 8
        this.tableName = ''
        this.columns = ''
    }

    open() {
        return new Promise((resolve, reject) => {
            let isOpen = plus.sqlite.isOpenDatabase({
                name: this.dbName,
                path: `_doc/data/${this.dbName}.db`
            })
            if (isOpen) {
                //console.log('数据库是否已经打开')
                resolve('')
            } else {
                plus.sqlite.openDatabase({
                    name: this.dbName,
                    path: `_doc/data/${this.dbName}.db`,
                    success: () => {
                        console.log('openDatabase success!')
                        resolve('')
                    },
                    fail: err => {
                        console.error('openDatabase failed: ', err)
                        reject('数据库打开失败')
                    }
                })
            }
        })
    }

    close() {
        plus.sqlite.closeDatabase({
            name: this.dbName,
            success: () => {
                console.log('closeDatabase success!')
            },
            fail: err => {
                console.log('closeDatabase failed: ', err)
            }
        })
    }

    executeSql(sql: any, noError?: any) {
        // console.log(sql);
        return new Promise((resolve, reject) => {
            plus.sqlite.executeSql({
                name: this.dbName,
                sql,
                success: e => {
                    //console.log('executeSql success!', JSON.stringify(e))
                    resolve(e)
                },
                fail: err => {
                    if (noError) {
                        resolve(err)
                    } else {
                        console.log('executeSql failed: ', JSON.stringify(err))
                        console.log('错误的语句: ', sql)
                        reject('执行增/删/改sql出错')
                    }
                }
            })
        })
    }

    selectSQL(sql: string) {
		// console.log(sql);
        return new Promise((resolve, reject) => {
            plus.sqlite.selectSql({
                name: this.dbName,
                sql,
                success: data => {
                    resolve(data)
                },
                fail: err => {
                    console.log('selectSql failed: ', err)
                    reject('查询数据出错')
                }
            })
        })
    }

    createRun({ tbName, tableColumns, tableIndex, columnAdd }: attr) {
        return new Promise((resolve, reject) => {
            if (tableIndex) {
                let sql = `create index if not exists "${tableIndex.name}" ON "${tbName}" (${tableIndex.key})`
                this.executeSql(sql)
                    .then(e => {
                        resolve(e)
                    })
                    .catch(err => {
                        reject(err)
                    })
            } else if (columnAdd) {
                let sql = `alter table "${tbName}" add column ${columnAdd.key} ${columnAdd.type}`
                this.executeSql(sql, true)
                    .then(e => {
                        resolve(e)
                    })
                    .catch(err => {
                        reject(err)
                    })
            } else {
                let columnsArray = []
                let primaryKeyArr = []
                for (let key in tableColumns) {
                    let val = tableColumns[key]
                    if (val.indexOf('PRIMARY KEY') >= 0 && val.indexOf('AUTOINCREMENT') < 0) {
                        val = val.replace('PRIMARY KEY', '')
                        primaryKeyArr.push('"' + this.toLine(key) + '"')
                    }
                    columnsArray.push('`' + this.toLine(key) + '` ' + val)
                }
                let sql = ''
                if (primaryKeyArr.length > 0) {
                    sql = `create table if not exists ${tbName} (${columnsArray.join(',')}, PRIMARY KEY (${primaryKeyArr.join(',')}))`
                } else {
                    sql = `create table if not exists ${tbName} (${columnsArray.join(',')})`
                }
                this.executeSql(sql)
                    .then(e => {
                        resolve(e)
                    })
                    .catch(err => {
                        reject(err)
                    })
            }
        })
    }

    insertRun({ tbName, tableColumns, data }: attr) {
        return new Promise((resolve, reject) => {
            if (!data) {
                resolve('')
                return
            }
            let dataArray: Array<any> = []
            if (Array.isArray(data)) {
                if (data.length <= 0) {
                    resolve('')
                    return
                }
                dataArray = data
            } else {
                dataArray = [data]
            }
            let columnMap = []
            let autoKey = ''
            for (let key in tableColumns) {
                if (tableColumns[key].indexOf('AUTOINCREMENT') < 0) {
                    columnMap.push(this.toLine(key))
                } else {
                    autoKey = key
                }
            }
            let valueArray: Array<any> = []
            dataArray.forEach(item => {
                let itemArray = []
                for (let key in tableColumns) {
                    if (tableColumns[key].indexOf('AUTOINCREMENT') < 0) {
                        itemArray.push(this.getColumnData(key, item[key], tableColumns))
                    }
                }
                valueArray.push(`(${itemArray.join(',')})`)
            })
            // 插入语句前部分
            const sql = `insert into ${tbName} (${columnMap.join(',')}) values ${valueArray.join(',')}`
            const idSql = `select max(last_insert_rowid()) as lastId from ${tbName}`
            this.executeSql(sql)
                .then(() => {
                    if (autoKey) {
                        this.selectSQL(idSql)
                            .then((rows: any) => {
                                let index = rows[0].lastId - dataArray.length
                                dataArray.forEach((item, i) => {
                                    item[autoKey] = index + i + 1
                                })
                                resolve(dataArray)
                            })
                            .catch(err => {
                                reject(err)
                            })
                    } else {
                        resolve(dataArray)
                    }
                })
                .catch(err => {
                    reject(err)
                })
        })
    }

    updateRun({ tbName, tableColumns, data, whereObj, where }: attr) {
        return new Promise((resolve, reject) => {
            if (!data || (!where && !whereObj)) {
                resolve('')
                return
            }
            let columnStrArr = []
            // set 部分
            for (let key in data) {
                // 剔除无用数据
                if (tableColumns[key]) {
                    columnStrArr.push(`${this.toLine(key)} = ${this.getColumnData(key, data[key], tableColumns)}`)
                }
            }
            // where 部分
            let query = where
            if (!query) {
                query = 'where'
                let i = 0
                for (let key in whereObj) {
                    query += ` ${i > 0 ? 'and' : ''} ${this.toLine(key)} = ${this.getColumnData(key, whereObj[key], tableColumns)}`
                    i++
                }
            }
            let sql = `update ${tbName} set ${columnStrArr.join(',')} ${query}`
            this.executeSql(sql)
                .then(() => {
                    resolve('')
                })
                .catch(err => {
                    reject(err)
                })
        })
    }

    updateByIdRun({ tbName, tableColumns, data }: attr) {
        return new Promise((resolve, reject) => {
            if (data) {
                if (Array.isArray(data)) {
                    if (data.length <= 0) {
                        resolve('')
                        return
                    }
                } else {
                    data = [data]
                }
            } else {
                resolve('')
                return
            }
            let len = data.length,
                errmsg = ''
            let primaryKeys = []
            // 查找主鍵
            for (let key in tableColumns) {
                if (tableColumns[key].indexOf('PRIMARY KEY') >= 0) {
                    primaryKeys.push(key)
                }
            }
            for (let item of data) {
                if (errmsg) {
                    break
                }
                let upSql = `update ${tbName} set`
                let upWSql = `where`
                // set where 部分
                let i = 0,
                    wi = 0
                for (let key in item) {
                    if (tableColumns[key]) {
                        if (primaryKeys.indexOf(key) < 0) {
                            upSql += i > 0 ? ',' : ''
                            upSql += ` ${this.toLine(key)} = ${this.getColumnData(key, item[key], tableColumns)}`
                            i++
                        } else {
                            upWSql += wi > 0 ? ' and' : ''
                            upWSql += ` ${this.toLine(key)} = ${this.getColumnData(key, item[key], tableColumns)}`
                            wi++
                        }
                    }
                }
                this.executeSql(`${upSql} ${upWSql}`)
                    .then(() => {
                        len--
                        if (len <= 0) {
                            resolve('')
                        }
                    })
                    .catch(err => {
                        errmsg += err
                        if (errmsg) {
                            reject(errmsg)
                        }
                    })
            }
        })
    }

    saveOrUpdateRun({ tbName, tableColumns, data }: attr) {
        return new Promise((resolve, reject) => {
            if (data) {
                if (!Array.isArray(data)) {
                    data = [data]
                }
            } else {
                resolve('')
                return
            }
            // 查找主鍵，可能多个
            let primaryKeys: Array<any> = []
            for (let key in tableColumns) {
                if (tableColumns[key].indexOf('PRIMARY KEY') >= 0) {
                    primaryKeys.push(key)
                }
            }
            // 筛选出所有主键值
            let idArr: Array<any> = []
            data.forEach((it: any) => {
                let obj = []
                let hasNull = false
                for (let itt of primaryKeys) {
                    if (it[itt]) {
                        obj.push(it[itt])
                    } else {
                        hasNull = true
                        break
                    }
                }
                if (!hasNull) {
                    const idstr = obj.join(`|| '_' ||`)
                    if (idArr.indexOf(idstr) < 0) {
                        idArr.push(idstr)
                    }
                }
            })
            // 查询表中是否存在
            let selectSql = `select`
            let si = 0
            primaryKeys.forEach(key => {
                selectSql += si > 0 ? ',' : ''
                selectSql += ` ${this.toLine(key)} as ${key}`
                si++
            })
            selectSql += ` from ${tbName} where ${primaryKeys.join(`|| '_' ||`)} in (${idArr.join(' , ')})`
            // console.log(selectSql)
            this.selectSQL(selectSql)
                .then((rows: any) => {
                    let insertArr: Array<any> = []
                    let updateByIdArr: Array<any> = []
                    data.forEach((item: any) => {
                        let has = rows.filter((it: any) => {
                            let same = true
                            primaryKeys.forEach(key => {
                                if (it[key] !== item[key]) {
                                    same = false
                                }
                            })
                            return same
                        })
                        if (has && has.length > 0) {
                            updateByIdArr.push(item)
                        } else {
                            insertArr.push(item)
                        }
                    })
                    let lock = 2
                    if (insertArr.length <= 0) {
                        lock--
                    }
                    if (updateByIdArr.length <= 0) {
                        lock--
                    }
                    if (updateByIdArr.length > 0) {
                        this.updateByIdRun({ tbName, tableColumns, data: updateByIdArr })
                            .then(() => {
                                lock--
                                if (lock <= 0) {
                                    resolve('')
                                }
                            })
                            .catch(err => {
                                reject(err)
                            })
                    }
                    if (insertArr.length > 0) {
                        this.insertRun({ tbName, tableColumns, data: insertArr })
                            .then(() => {
                                lock--
                                if (lock <= 0) {
                                    resolve('')
                                }
                            })
                            .catch(err => {
                                reject(err)
                            })
                    }
                })
                .catch(err => {
                    reject(err)
                })
        })
    }

    queryRun({ tbName, tableColumns, selects, whereObj, where, orderBy, limit }: attr) {
        return new Promise((resolve, reject) => {
            // selects 部分
            let selectStr = selects
            if (selectStr) {
                for (let key in tableColumns) {
                    if (selectStr.match(this.toLine(key)) && !selectStr.match(this.toLine(key) + '\\s+[Aa][Ss]') && !selectStr.match('(' + this.toLine(key) + ')')) {
                        selectStr = selectStr.replace(this.toLine(key), this.toLine(key) + ' as ' + key)
                    }
                }
            } else {
                selectStr = ''
                let i = 0
                for (let key in tableColumns) {
                    selectStr += i++ > 0 ? ',' : ''
                    selectStr += this.toLine(key) + ' as ' + key
                }
            }
            // where 部分
            let query = where
            if (!query && whereObj) {
                query = 'where'
                let i = 0
                for (let key in whereObj) {
                    if (i > 0) {
                        query += ' and'
                    }
                    query += ` ${this.toLine(key)} = ${this.getColumnData(key, whereObj[key], tableColumns)}`
                    i++
                }
            }
            const sql = `select ${selectStr} from ${tbName} ${query} ${orderBy ? orderBy : ''} ${limit ? 'limit ' + limit : ''}`
            this.selectSQL(sql)
                .then(rows => {
                    resolve(rows)
                })
                .catch(err => {
                    reject(err)
                })
        })
    }

    queryCustomRun({ sql }: { sql: string }) {
        return new Promise((resolve, reject) => {
            this.selectSQL(sql)
                .then(rows => {
                    resolve(rows)
                })
                .catch(err => {
                    reject(err)
                })
        })
    }

    deleteRun({ tbName, tableColumns, whereObj, where }: { tbName: string; tableColumns: any; whereObj: any; where: any }) {
        return new Promise((resolve, reject) => {
            // where 部分
            let query = where
            if (!query) {
                query = 'where'
                let i = 0
                for (let key in whereObj) {
                    if (i > 0) {
                        query += ' and'
                    }
                    query += ` ${this.toLine(key)} = ${this.getColumnData(key, whereObj[key], tableColumns)}`
                    i++
                }
            }
            const sql = `delete from ${tbName} ${query}`
            this.executeSql(sql)
                .then(() => {
                    resolve('')
                })
                .catch(err => {
                    reject(err)
                })
        })
    }

    executeOptions({ model, selects, data, whereObj, where, tableIndex, columnAdd, orderBy, limit, sql }: attr) {
        return {
            dao: this,
            model: model ? model : this.QUERY,
            tbName: this.tableName,
            tableColumns: this.columns,
            columnAdd,
            tableIndex,
            selects,
            data,
            whereObj,
            where,
            orderBy,
            limit,
            sql
        }
    }

    executeOptionsByTableIndex(tableIndexArr: Array<any>) {
        let options: Array<any> = []
        tableIndexArr.forEach(item => {
            options.push(
                this.executeOptions({
                    model: this.CREATE,
                    tbName: this.tableName,
                    tableIndex: item
                })
            )
        })
        return options
    }

    executeOptionsByColumnAdd(columnAdd: Array<any>) {
        let options: Array<any> = []
        columnAdd.forEach(item => {
            options.push(
                this.executeOptions({
                    model: this.CREATE,
                    tbName: this.tableName,
                    columnAdd: item
                })
            )
        })
        return options
    }

    dealExecute(arr: Array<any>, i?: any) {
        if (!i) i = 0
        return new Promise((resolve, reject) => {
            let methods = 'queryRun'
            let result: any = null
            switch (arr[i].model) {
                case this.QUERY:
                    result = this.queryRun(arr[i])
                    break
                case this.INSERT:
                    result = this.insertRun(arr[i])
                    break
                case this.UPDATE:
                    result = this.updateRun(arr[i])
                    break
                case this.DELETE:
                    result = this.deleteRun(arr[i])
                    break
                case this.CREATE:
                    result = this.createRun(arr[i])
                    break
                case this.UPDATE_BY_ID:
                    result = this.updateByIdRun(arr[i])
                    break
                case this.SAVE_OR_UPDATE:
                    result = this.saveOrUpdateRun(arr[i])
                    break
                case this.QUERY_CUSTOM:
                    result = this.queryCustomRun(arr[i])
                    break
                default:
                    console.log('意外')
            }
            result
                .then((rows: any) => {
                    i++
                    if (i < arr.length) {
                        this.dealExecute(arr, i)
                            .then(rows => {
                                resolve(rows)
                            })
                            .catch(err => {
                                reject(err)
                            })
                    } else {
                        resolve(rows)
                    }
                })
                .catch((err: any) => {
                    reject(err)
                })
        })
    }

    executeTransaction(arr: Array<any> | any) {
        if (arr) {
            if (!Array.isArray(arr)) {
                arr = [arr]
            }
        }
        return new Promise((resolve, reject) => {
            if (arr && arr.length > 0) {
                // 开启数据库
                this.open()
                    .then(() => {
                        // 开启事务
                        plus.sqlite.transaction({
                            name: this.dbName,
                            operation: 'begin',
                            success: () => {
                                this.dealExecute(arr)
                                    .then(rows => {
                                        // 提交事务
                                        plus.sqlite.transaction({
                                            name: this.dbName,
                                            operation: 'commit',
                                            success: () => {
                                                resolve(rows)
                                            },
                                            fail: commitErr => {
                                                console.log('提交事务失败', commitErr)
                                                reject('提交事务失败')
                                            }
                                        })
                                    })
                                    .catch(err => {
                                        // 回滚事务
                                        plus.sqlite.transaction({
                                            name: this.dbName,
                                            operation: 'rollback',
                                            success: () => {
                                                reject(err)
                                            },
                                            fail: rollbackErr => {
                                                console.log('回滚事务失败', rollbackErr)
                                                reject('回滚事务失败')
                                            }
                                        })
                                    })
                            },
                            fail: beginErr => {
                                console.log('开启事务失败', beginErr)
                                reject('开启事务失败')
                            }
                        })
                    })
                    .catch(err => {
                        reject(err)
                    })
            } else {
                reject('执行数据不能为空')
            }
        })
    }

    execute(arr: Array<any> | any) {
        if (arr) {
            if (!Array.isArray(arr)) {
                arr = [arr]
            }
        }
        return new Promise((resolve, reject) => {
            if (arr && arr.length > 0) {
                // 开启数据库
                this.open()
                    .then(() => {
                        this.dealExecute(arr)
                            .then(rows => {
                                resolve(rows)
                            })
                            .catch(err => {
                                reject(err)
                            })
                    })
                    .catch(err => {
                        reject(err)
                    })
            } else {
                reject('执行数据不能为空')
            }
        })
    }

    getColumnData(key: string, value: any, columns: any) {
        if (columns[key].indexOf('VARCHAR') >= 0 || columns[key].indexOf('TEXT') >= 0 || columns[key].indexOf('datetime') >= 0) {
            return `'${value ? value : ''}'`
        } else if (columns[key].indexOf('BIGINT') >= 0 || columns[key].indexOf('INT') >= 0 || columns[key].indexOf('tinyint') >= 0) {
            return value ? (value === true ? 1 : value) : 0
        } else {
            return value
        }
    }

    // 下划线转换驼峰
    toHump(name: string) {
        return name.replace(/\_(\w)/g, (all, letter) => {
            return letter.toUpperCase()
        })
    }

    // 驼峰转换下划线
    toLine(name: string) {
        return name.replace(/([A-Z])/g, '_$1').toLowerCase()
    }

    // 部分公共业务
    getList({ whereObj, selects, where, orderBy, limit }: attr) {
        let a = this.executeOptions({
            model: this.QUERY,
            selects,
            whereObj,
            where,
            orderBy,
            limit
        })
        return this.execute(a)
    }

    getLastTime({ whereObj, where, selects }: attr) {
        let a = this.executeOptions({
            model: this.QUERY,
            selects: selects ? selects : 'max(update_time) as lastTime',
            whereObj,
            where
        })
        return this.execute(a)
    }

    getCount({ whereObj, where, selects }: attr) {
        let a = this.executeOptions({
            model: this.QUERY,
            selects: 'count(1) as count',
            whereObj,
            where
        })
        return this.execute(a)
    }

    updateById(data: any) {
        let a = this.executeOptions({
            model: this.UPDATE_BY_ID,
            data
        })
        return this.execute(a)
    }

    update({ data, whereObj, where }: attr) {
        let a = this.executeOptions({
            model: this.UPDATE,
            data,
            whereObj,
            where
        })
        return this.execute(a)
    }

    delete({ whereObj, where }: attr) {
        let a = this.executeOptions({
            model: this.DELETE,
            whereObj,
            where
        })
        return this.execute(a)
    }

    save(data: any) {
        let a = this.executeOptions({
            model: this.INSERT,
            data
        })
        return this.execute(a)
    }

    saveOrUpdate(data: any) {
        let a = this.executeOptions({
            model: this.SAVE_OR_UPDATE,
            data
        })
        return this.execute(a)
    }
}
